﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Web.UI.WebControls;

namespace DCTLib.Common
{
    public class DatatableHelper
    {
        private static bool RowEqual(object[] Values, object[] OtherValues)
        {
            if (Values == null)
                return false;

            for (int i = 0; i < Values.Length; i++)
            {
                if (!Values[i].Equals(OtherValues[i]))
                    return false;
            }
            return true;
        }

        public static DataTable Join(DataTable First, DataTable Second, DataColumn[] FJC, DataColumn[] SJC)
        {
            //Create Empty Table
            DataTable table = new DataTable("Join");

            // Use a DataSet to leverage DataRelation
            using (DataSet ds = new DataSet())
            {
                //Add Copy of Tables
                ds.Tables.AddRange(new DataTable[] { First.Copy(), Second.Copy() });

                //Identify Joining Columns from First
                DataColumn[] parentcolumns = new DataColumn[FJC.Length];
                for (int i = 0; i < parentcolumns.Length; i++)
                {
                    parentcolumns[i] = ds.Tables[0].Columns[FJC[i].ColumnName];
                }

                //Identify Joining Columns from Second
                DataColumn[] childcolumns = new DataColumn[SJC.Length];
                for (int i = 0; i < childcolumns.Length; i++)
                {
                    childcolumns[i] = ds.Tables[1].Columns[SJC[i].ColumnName];
                }

                //Create DataRelation
                DataRelation r = new DataRelation(string.Empty, parentcolumns, childcolumns, false);
                ds.Relations.Add(r);

                //Create Columns for JOIN table
                for (int i = 0; i < First.Columns.Count; i++)
                {
                    table.Columns.Add(First.Columns[i].ColumnName, First.Columns[i].DataType);
                }

                for (int i = 0; i < Second.Columns.Count; i++)
                {
                    //Beware Duplicates
                    if (!table.Columns.Contains(Second.Columns[i].ColumnName))
                    {
                        table.Columns.Add(Second.Columns[i].ColumnName, Second.Columns[i].DataType);
                    }
                    else
                    {
                        table.Columns.Add(Second.Columns[i].ColumnName + "_Second", Second.Columns[i].DataType);
                    }
                }

                //Loop through First table
                table.BeginLoadData();

                foreach (DataRow firstrow in ds.Tables[0].Rows)
                {
                    //Get "joined" rows
                    DataRow[] childrows = firstrow.GetChildRows(r);
                    if (childrows != null && childrows.Length > 0)
                    {
                        object[] parentarray = firstrow.ItemArray;

                        foreach (DataRow secondrow in childrows)
                        {
                            object[] secondarray = secondrow.ItemArray;
                            object[] joinarray = new object[parentarray.Length + secondarray.Length];
                            Array.Copy(parentarray, 0, joinarray, 0, parentarray.Length);
                            Array.Copy(secondarray, 0, joinarray, parentarray.Length, secondarray.Length);

                            table.LoadDataRow(joinarray, true);
                        }
                    }
                }
                table.EndLoadData();
            }

            return table;
        }

        public static DataTable Join(DataTable First, DataTable Second, DataColumn FJC, DataColumn SJC)
        {
            return Join(First, Second, new DataColumn[] { FJC }, new DataColumn[] { SJC });
        }

        public static DataTable Join(DataTable First, DataTable Second, string FirstJoinColumnName, string SecondJoinColumnName)
        {
            if (First.Columns[FirstJoinColumnName] == null)
                throw new System.Exception("Column " + FirstJoinColumnName + " doesnot exist");

            if (Second.Columns[SecondJoinColumnName] == null)
                throw new System.Exception("Column " + SecondJoinColumnName + " doesnot exist");

            return Join(First, Second, new DataColumn[] { First.Columns[FirstJoinColumnName] }, new DataColumn[] { Second.Columns[SecondJoinColumnName] });
        }

        #region Intersect methods
        /// <summary>
        /// Return a datatable as a result of INTERSECT query between 2 specified datatables.
        /// </summary>
        /// <param name="First">First DataTable</param>
        /// <param name="Second">Second Datatable</param>
        /// <returns>Return a datatable as a result of INTERSECT query between 2 specified datatables.</returns>
        public static DataTable Intersect(DataTable First, DataTable Second)
        {
            //Get reference to Columns in First
            DataColumn[] firstcolumns = new DataColumn[First.Columns.Count];

            for (int i = 0; i < firstcolumns.Length; i++)
            {
                firstcolumns[i] = First.Columns[i];
            }

            //Get reference to Columns in Second
            DataColumn[] secondcolumns = new DataColumn[Second.Columns.Count];

            for (int i = 0; i < secondcolumns.Length; i++)
            {
                secondcolumns[i] = Second.Columns[i];
            }

            //JOIN ON all columns
            DataTable table = Join(First, Second, firstcolumns, secondcolumns);
            table.TableName = "Intersect";
            return table;
        }
        #endregion

        #region Distinct methods
        /// <summary>
        /// Select distinct rows by specified column(s).
        /// </summary>
        /// <param name="Table">Datatable</param>
        /// <param name="Columns">Columns</param>
        public static DataTable Distinct(DataTable Table, DataColumn[] Columns)
        {
            //Empty table
            DataTable table = new DataTable("Distinct");

            //Sort variable
            string sort = string.Empty;

            //Add Columns & Build Sort expression
            for (int i = 0; i < Columns.Length; i++)
            {
                table.Columns.Add(Columns[i].ColumnName, Columns[i].DataType);
                sort += Columns[i].ColumnName + ",";
            }

            //Select all rows and sort
            DataRow[] sortedrows = Table.Select(string.Empty, sort.Substring(0, sort.Length - 1));

            object[] currentrow = null;
            object[] previousrow = null;

            table.BeginLoadData();

            foreach (DataRow row in sortedrows)
            {
                //Current row
                currentrow = new object[Columns.Length];

                for (int i = 0; i < Columns.Length; i++)
                {
                    currentrow[i] = row[Columns[i].ColumnName];
                }

                //Match Current row to previous row
                if (!RowEqual(previousrow, currentrow))
                    table.LoadDataRow(currentrow, true);

                //Previous row
                previousrow = new object[Columns.Length];

                for (int i = 0; i < Columns.Length; i++)
                {
                    previousrow[i] = row[Columns[i].ColumnName];
                }
            }
            table.EndLoadData();
            return table;
        }

        public static DataTable Distinct(DataTable Table, DataColumn Column)
        {
            return Distinct(Table, new DataColumn[] { Column });
        }

        public static DataTable Distinct(DataTable Table, string Column)
        {
            return Distinct(Table, Table.Columns[Column]);
        }

        public static DataTable Distinct(DataTable Table, params string[] Columns)
        {
            DataColumn[] columns = new DataColumn[Columns.Length];

            for (int i = 0; i < Columns.Length; i++)
            {
                columns[i] = Table.Columns[Columns[i]];
            }
            return Distinct(Table, columns);
        }

        public static DataTable Distinct(DataTable Table)
        {
            DataColumn[] columns = new DataColumn[Table.Columns.Count];

            for (int i = 0; i < Table.Columns.Count; i++)
            {
                columns[i] = Table.Columns[i];
            }
            return Distinct(Table, columns);
        }
        #endregion

        #region Union methods
        public static DataTable Union(DataTable firstDataTable, DataTable secondDataTable)
        {
            if (!firstDataTable.Columns.Equals(secondDataTable.Columns))
            {
                throw new System.Exception("Two DataTable must have same columns");
            }

            DataTable result = new DataTable("UNION");
            if ((firstDataTable == null) && (secondDataTable == null))
            {
                return null;
            }
            else
            {
                if (firstDataTable != null)
                {
                    result = firstDataTable;
                    for (int i = 0; i < secondDataTable.Rows.Count; i++)
                    {
                        DataRow row = secondDataTable.Rows[i];
                        result.Rows.Add(row);
                    }
                }
                else if (secondDataTable != null)
                {
                    result = secondDataTable;
                }
            }

            return result;
        }
        #endregion

        #region ShuffleTable methods
        /// <summary>
        /// Randomizes the order of the rows in a DataTable by pulling out a single row and moving it to the end.  This
        /// process is repeated 3x per row in the table.
        /// </summary>
        /// <param name="inputTable"></param>
        /// <returns></returns>
        public static DataTable ShuffleTable(DataTable inputTable)
        {
            if (ValidationHelper.HasRow(inputTable))
            {
                inputTable.Columns.Add(new DataColumn("___RandomNum", Type.GetType("System.Int32")));
                Random random = new Random();
                for (int i = 0; i < inputTable.Rows.Count; i++)
                {
                    inputTable.Rows[i]["___RandomNum"] = random.Next(1000);
                }

                DataView dv = new DataView(inputTable);
                dv.Sort = "___RandomNum";

                return dv.Table;
            }
            else
            {
                return inputTable;
            }
        }
        #endregion

        public static void HasNoRow(DataTable dt, string columnName, GridView gridView)
        {
            if (dt == null)
            {
                dt = new DataTable();
                dt.Columns.Add(columnName);
            }

            DataRow dr = dt.NewRow();
            dr[columnName] = "There was no result found";
            dt.Rows.Add(dr);
            gridView.DataSource = dt;
            //gridView.DataBind();
        }
    }
}
